package org.BUPT.modules.system.service.impl;

import org.BUPT.common.data.PageSort;
import org.BUPT.modules.system.domain.Subject;
import org.BUPT.modules.system.domain.User;
import org.BUPT.modules.system.repository.SubjectRepository;
import org.BUPT.modules.system.repository.UserRepository;
import org.BUPT.modules.system.service.SubjectService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

@Service
public class SubjectServiceImpl implements SubjectService {

    public static int ADMIN=1;
    public static int TEACHER=2;
    public static int STUDENT=3;
    String url = "jdbc:mysql://localhost:3306/timo";
    String username = "root";
    String password = "li2843585";
    Connection conn = null;
    public SubjectServiceImpl(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }


    }

    @Autowired
    private SubjectRepository subjectRepository;

    @Autowired
    private UserRepository userRepository;

    @Override
    public List<Subject> getListByExample(Example<Subject> example, Sort sort) {
        return null;
    }

    @Override
    public Page<Subject> getPageList(Example<Subject> example) {
        // 创建分页对象
        PageRequest page = PageSort.pageRequest(Sort.Direction.ASC);
        return subjectRepository.findAll(example, page);
    }

    @Override
    public Subject save(Subject dict) {
        return subjectRepository.save(dict);
    }

    @Override
    public List<User> getSutents(Long subjectId) {
        Statement st = null;
        ArrayList<Long> stuids=new ArrayList<>();
        //3.获取用于向数据库发送sql语句的statement
        try {
            st = conn.createStatement();
            String sql = "select sys_user.id from sys_user,sys_user_subjects,sys_subject where sys_user.id=sys_user_subjects.user_id and sys_user_subjects.subjects_id=sys_subject.id and  sys_user.role=3 and sys_subject.id="+subjectId+";";
            ResultSet rs=st.executeQuery(sql);
            //循环取出(id)
            while(rs.next())
            {
                String id=rs.getString(1);//1代表数据库中表的列数，id在第一列也可以("id")！！！
                stuids.add(Long.valueOf(id));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return userRepository.findAllById(stuids);
    }

    @Override
    public List<User> getTeachers(Long subjectId) {
        Statement st = null;
        ArrayList<Long> stuids=new ArrayList<>();
        //3.获取用于向数据库发送sql语句的statement
        try {
            st = conn.createStatement();
            String sql = "select sys_user.id from sys_user,sys_user_subjects,sys_subject where sys_user.id=sys_user_subjects.user_id and sys_user_subjects.subjects_id=sys_subject.id and  sys_user.role=2 and sys_subject.id="+subjectId+";";
            ResultSet rs=st.executeQuery(sql);
            //循环取出(id)
            while(rs.next())
            {
                String id=rs.getString(1);//1代表数据库中表的列数，id在第一列也可以("id")！！！
                stuids.add(Long.valueOf(id));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return userRepository.findAllById(stuids);
    }

    @Override
    public List<User> getSutentsNotIn(Long subjectId) {

        List<User> studentin=this.getSutents(subjectId);
        List<User> students=userRepository.findAll();

        ArrayList<Long> stuids=new ArrayList<>();
        for(User user:students){
            if(!studentin.contains(user)){
                stuids.add(user.getId());
            }
        }
        return filterStudent(userRepository.findByIdIn(stuids));
    }

    @Override
    public List<User> getTeachersNotIn(Long subjectId) {

        List<User> teachers=this.getTeachers(subjectId);
        ArrayList<Long> teaids=new ArrayList<>();
        for(User user:teachers){
            teaids.add(user.getId());
        }
        return filterTeacher(userRepository.findByIdNotIn(teaids));
    }

    @Override
    public void addStudent(Long subjectId, Long studentId) {
        Statement st = null;
        try {
            st = conn.createStatement();
            String sql = String.format("INSERT INTO sys_user_subjects VALUES(%s,%s);",studentId,subjectId);
            st.execute(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public Subject getSubject(Long subjectId) {
        return subjectRepository.findById(subjectId).get();
    }

    /**
     * 取出所有和该名用户相关的课程
     * @param userId
     * @return
     */
    @Override
    public List<Subject> getSubjectsByUserId(Long userId) {
        List<Subject> list=new ArrayList<>();
        Statement st = null;
        try {
            st = conn.createStatement();
            String sql = String.format("select sys_subject.id from sys_user,sys_subject,sys_user_subjects where sys_user.id=sys_user_subjects.user_id and sys_user_subjects.subjects_id =sys_subject.id and sys_user.id=%s;",userId);
            ResultSet rs=st.executeQuery(sql);
            while(rs.next())
            {
                String id=rs.getString(1);//1代表数据库中表的列数，id在第一列也可以("id")！！！
                list.add(subjectRepository.findById(Long.valueOf(id)).get());
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return list;
    }

    @Override
    public void deleteStudentByUserId(Long subjectId,Long studentId) {
        Statement st = null;
        try {
            st = conn.createStatement();
            String sql = String.format("DELETE sys_user_subjects WHERE user_id=%s AND subjects_id=%s;",studentId,subjectId);
            st.execute(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public void deleteTeacherByUserId(Long subjectId,Long studentId) {

    }

    private List<User> filterStudent(List<User> students){
        ArrayList<User> stuids=new ArrayList<>();
        for(User user:students){
            if(user.getRole()==null){
                continue;
            }
            if(user.getRole().getId()==STUDENT)
            stuids.add(user);
        }
        return stuids;
    }

    private List<User> filterTeacher(List<User> teachers){
        ArrayList<User> stuids=new ArrayList<>();
        for(User user:teachers){
            if(user.getRole().getId()==TEACHER)
                stuids.add(user);
        }
        return stuids;
    }


}
